''' 考勤管理 '''
import sys,traceback,logging,datetime
import pymysql
from dbutils.pooled_db import PooledDB 
from utils.db_config import mysqlInfo
import math
from datetime import datetime, timedelta
import pandas as pd

logger = logging.getLogger('app')

# 创建数据库连接
def connect_db():
    pool=PooledDB(creator=pymysql,mincached=2, maxcached=5,maxshared=3, maxconnections=6, blocking=True,host=mysqlInfo['host'],port=mysqlInfo['port'],
        user=mysqlInfo['user'],password=mysqlInfo['password'],database=mysqlInfo['database'],charset=mysqlInfo['charset'],cursorclass=pymysql.cursors.DictCursor)
    return pool.connection()

'''
考勤信息
'''
# 获取考勤统计数据
def getstatisticaldata(param):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:

        sql = "with checktime as ( " \
              + "   select " \
              + "       a.employee_id,a.device_id,a.indate,a.intime,b.outtime  " \
              + "   from ( " \
              + "       select " \
              + "           employee_id,device_id,DATE_FORMAT(check_in_time, '%Y-%m-%d') as indate, min(check_in_time) as intime  " \
              + "               from employee_check_record  " \
              + "       group by employee_id,device_id,indate " \
              + "       ) as a  " \
              + "       inner join ( " \
              + "       select " \
              + "           employee_id,device_id,DATE_FORMAT(check_out_time, '%Y-%m-%d') as outdate, max(check_out_time) as outtime  " \
              + "               from employee_check_record  " \
              + "       group by employee_id,device_id,outdate " \
              + "       ) as b  " \
              + "       on a.employee_id = b.employee_id and a.indate = b.outdate " \
              + ")   " \
              + "   select  " \
              + "       p.staffsize,concat(DATE_FORMAT(m_on.inyearmonth, '%d'), '日') as indays,coalesce(m.managercnt,0) as managercnt,coalesce(w.workercnt,0) as workercnt " \
              + "       ,coalesce(m_on.onsitecnt,0) as m_onsitecnt,coalesce(w_on.onsitecnt,0) as w_onsitecnt,coalesce(m.managercnt - m_on.onsitecnt,0) as m_unsitecnt " \
              + "       ,coalesce(w.workercnt - w_on.onsitecnt,0) as w_unsitecnt " \
              + "   from " \
              + "       projects p " \
              + "   left outer join " \
              + "   (  " \
              + "       select  " \
              + "           project_id,count(id) as managercnt " \
              + "       from  " \
              + "           employee " \
              + "       where  " \
              + "           role = '1' " \
              + "       group by project_id " \
              + "   ) m " \
              + "   on  " \
              + "       p.id = m.project_id " \
              + "   left outer join   " \
              + "   ( " \
              + "       select  " \
              + "           project_id,count(id) as workercnt " \
              + "       from " \
              + "           employee " \
              + "       where  " \
              + "           role = '0' " \
              + "       group by project_id " \
              + "   ) w " \
              + "   on  " \
              + "       p.id = w.project_id " \
              + "   left outer join " \
              + "   ( " \
              + "       select " \
              + "           u.project_id,a.indate as inyearmonth,count(a.employee_id) as onsitecnt   " \
              + "       from checktime a  " \
              + "       left outer join " \
              + "           employee u " \
              + "       on  " \
              + "           u.id = a.employee_id " \
              + "       and role = '1' " \
              + "       group by u.project_id,inyearmonth " \
              + "    ) m_on " \
              + "    on  " \
              + "       p.id = m_on.project_id " \
              + "    left outer join   " \
              + "    ( " \
              + "        select " \
              + "            u.project_id,a.indate as inyearmonth,count(a.employee_id) as onsitecnt   " \
              + "        from checktime a  " \
              + "        left outer join " \
              + "            employee u " \
              + "        on  " \
              + "            u.id = a.employee_id " \
              + "        and role = '0' " \
              + "        group by u.project_id,inyearmonth " \
              + "     ) w_on " \
              + "     on  " \
              + "         p.id = w_on.project_id " \
              + "     where 1=1" 
        if param['project'] is not None and param['project'] != '':
            sql += " and p.name like '%" + param['project'] + "%' "
        else:
            sql += " and p.id = (select id from projects order by id limit 1) "
        if param['kinmuym'] is not None and param['kinmuym'] != '':
            sql += " and DATE_FORMAT(m_on.inyearmonth, '%Y年%m月') = '" + param['kinmuym'] + "' "
        else:
            sql += " and DATE_FORMAT(m_on.inyearmonth, '%Y-%m') = (select DATE_FORMAT(check_in_time, '%Y-%m') as checktime from employee_check_record order by checktime desc limit 1) "
        sql += " order by indays "
        cur.execute(sql)
        lisrec = []
        for row in cur.fetchall():
            rowdict = {}
            rowdict['staffsize'] = row['staffsize']
            rowdict['indays'] = row['indays']
            rowdict['managercnt'] = row['managercnt']
            rowdict['workercnt'] = row['workercnt']
            rowdict['m_onsitecnt'] = row['m_onsitecnt']
            rowdict['w_onsitecnt'] = row['w_onsitecnt']
            rowdict['m_unsitecnt'] = row['m_unsitecnt']
            rowdict['w_unsitecnt'] = row['w_unsitecnt']
            lisrec.append(rowdict)

        # 获取当前日期
        if param['kinmuym'] is not None and param['kinmuym'] != '':
            now = pd.Timestamp(param['kinmuym'].replace('年','-').replace('月','-') + '01')
        else:
            now = datetime.now()
         
        # 设置当前月的第一天
        start_date = datetime(now.year, now.month, 1)
         
        # 设置下个月的第一天，然后减去一天得到当前月的最后一天
        end_date = (start_date + timedelta(days=31)).replace(day=1) - timedelta(days=1)
         
        # 使用列表推导式获取当前月的所有日期
        all_days = [start_date + timedelta(days=i) for i in range((end_date - start_date).days + 1)]

        out_days = []
        # 打印结果
        for day in all_days:
            out_days.append(day.strftime('%d') + '日')
        
        lm_onsitecnt = [0] * len(out_days)
        lw_onsitecnt = [0] * len(out_days)
        lm_unsitecnt = [0] * len(out_days)
        lw_unsitecnt = [0] * len(out_days)
        for i in range(len(out_days)):
            for rec in lisrec:
                if out_days[i] == rec['indays']:
                    lm_onsitecnt[i] = rec['m_onsitecnt']
                    lw_onsitecnt[i] = rec['w_onsitecnt']
                    lm_unsitecnt[i] = rec['m_unsitecnt']
                    lw_unsitecnt[i] = rec['w_unsitecnt']
                    break
        
        data = {}
        data['lxAxis'] = out_days
        data['lm_onsitecnt'] = lm_onsitecnt
        data['lw_onsitecnt'] = lw_onsitecnt
        data['lm_unsitecnt'] = lm_unsitecnt
        data['lw_unsitecnt'] = lw_unsitecnt
        result['code'] = 200
        result['result'] = data
        result['message'] = 'ok'
        
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

# 获取考勤年月
def getkinmuym(param):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:
        sql = "  select " \
              + "   DATE_FORMAT(a.check_in_time, '%Y年%m月') as lindate,DATE_FORMAT(a.check_in_time, '%Y-%m') as vindate " \
              + "from employee_check_record a " \
              + "inner join  " \
              + "    employee u " \
              + "on " \
              + "   u.id = a.employee_id "
        if param['project'] is not None and param['project'] != '':
            sql += " inner join projects p on p.id = u.project_id and p.name like '%" + param['project'] + "%' "
        else:
            sql += " inner join projects p on p.id = u.project_id and p.id = (select id from projects order by id limit 1) "
        sql += " group by lindate,vindate order by vindate desc "
        logger.info(sql)
        cur.execute(sql)
        lisrec = []
        for row in cur.fetchall():
            rowdict = {}
            rowdict['label'] = row['lindate']
            rowdict['value'] = row['lindate']
            lisrec.append(rowdict)

        result['code'] = 200
        result['result'] = lisrec
        result['message'] = 'ok'
        
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

# 获取考勤项目
def getproject():
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:
        sql = " select id,name from projects group by id,name " 
        cur.execute(sql)
        lisrec = []
        for row in cur.fetchall():
            rowdict = {}
            rowdict['value'] = row['name']
            rowdict['label'] = row['name']
            lisrec.append(rowdict)
        result['code'] = 200
        result['result'] = lisrec
        result['message'] = 'ok'
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

# 获取当日考勤信息
def getkinmuinfo(param):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:
        sql = "with checktime as ( " \
              + "   select " \
              + "       a.employee_id,a.device_id,a.indate,a.intime,b.outtime  " \
              + "   from ( " \
              + "       select " \
              + "           employee_id,device_id,DATE_FORMAT(check_in_time, '%Y-%m-%d') as indate, min(check_in_time) as intime  " \
              + "               from employee_check_record  " \
              + "       group by employee_id,device_id,indate " \
              + "       ) as a  " \
              + "       inner join ( " \
              + "       select " \
              + "           employee_id,device_id,DATE_FORMAT(check_out_time, '%Y-%m-%d') as outdate, max(check_out_time) as outtime  " \
              + "               from employee_check_record  " \
              + "       group by employee_id,device_id,outdate " \
              + "       ) as b  " \
              + "       on a.employee_id = b.employee_id and a.indate = b.outdate " \
              + ")   " \
              + "select  " \
              + "   p.staffsize,coalesce(m.managercnt,0) as managercnt,coalesce(w.workercnt,0) as workercnt " \
              + "   ,coalesce(m_on.onsitecnt,0) as m_onsitecnt,coalesce(w_on.onsitecnt,0) as w_onsitecnt,coalesce(m.managercnt - m_on.onsitecnt,0) as m_unsitecnt " \
              + "   ,coalesce(w.workercnt - w_on.onsitecnt,0) as w_unsitecnt,coalesce(m_nocheckin.m_nocheckincnt,0) as m_nocheckincnt " \
              + "   ,coalesce(w_nocheckin.w_nocheckincnt,0) as w_nocheckincnt " \
              + "from " \
              + "   projects p " \
              + "left outer join   " \
              + "   ( " \
              + "       select  " \
              + "           project_id,count(id) as managercnt " \
              + "       from  " \
              + "           employee " \
              + "               where  " \
              + "           role = '1' " \
              + "               group by project_id " \
              + "   ) m " \
              + "on  " \
              + "   p.id = m.project_id " \
              + "left outer join   " \
              + "   ( " \
              + "       select  " \
              + "           project_id,count(id) as workercnt " \
              + "       from  " \
              + "           employee " \
              + "               where  " \
              + "           role = '0' " \
              + "               group by project_id " \
              + "   ) w " \
              + "on  " \
              + "   p.id = w.project_id " \
              + "left outer join  " \
              + "   ( " \
              + "       select " \
              + "           u.project_id,count(case  " \
              + "           when (select max(e.check_in_time) from employee_check_record e where e.employee_id = a.employee_id and DATE_FORMAT(e.check_in_time,'%Y-%m-%d') = a.indate) < a.outtime and DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s') > DATE_FORMAT(a.outtime,'%Y-%m-%d %H:%i:%s') " \
              + "           then 1 else 0 end) as onsitecnt   " \
              + "       from checktime a  " \
              + "       inner join " \
              + "           employee u  " \
              + "       on  " \
              + "           u.id = a.employee_id " \
              + "       and role = '1' " \
              + "       where DATE_FORMAT(now(),'%Y-%m-%d') = a.indate " \
              + "               group by u.project_id " \
              + "   ) m_on " \
              + "on  " \
              + "   p.id = m_on.project_id " \
              + "left outer join   " \
              + "   ( " \
              + "       select " \
              + "           u.project_id,count(case  " \
              + "           when (select max(e.check_in_time) from employee_check_record e where e.employee_id = a.employee_id and DATE_FORMAT(e.check_in_time,'%Y-%m-%d') = a.indate) < a.outtime and DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s') > DATE_FORMAT(a.outtime,'%Y-%m-%d %H:%i:%s') " \
              + "           then 1 else 0 end) as onsitecnt   " \
              + "       from checktime a  " \
              + "       inner join " \
              + "           employee u " \
              + "       on  " \
              + "           u.id = a.employee_id " \
              + "       and role = '0' " \
              + "       where DATE_FORMAT(now(),'%Y-%m-%d') = a.indate " \
              + "       group by u.project_id " \
              + "   ) w_on " \
              + "on  " \
              + "   p.id = w_on.project_id " \
              + "left outer join  " \
              + "   ( " \
              + "       select  " \
              + "           project_id,count(id) as m_nocheckincnt " \
              + "       from  " \
              + "           employee " \
              + "       where  " \
              + "           role = '1' and id not in (select employee_id from checktime where DATE_FORMAT(now(),'%Y-%m-%d') = indate group by employee_id) " \
              + "       group by project_id " \
              + "   ) m_nocheckin " \
              + "on  " \
              + "   p.id = m_nocheckin.project_id " \
              + "left outer join  " \
              + "   ( " \
              + "       select  " \
              + "           project_id,count(id) as w_nocheckincnt " \
              + "       from  " \
              + "           employee " \
              + "       where  " \
              + "           role = '0' and id not in (select employee_id from checktime where DATE_FORMAT(now(),'%Y-%m-%d') = indate group by employee_id) " \
              + "       group by project_id " \
              + "   ) w_nocheckin " \
              + "on  " \
              + "   p.id = w_nocheckin.project_id " \
              + "     where 1=1" 
        if param['project'] is not None and param['project'] != '':
            sql += " and p.name like '%" + param['project'] + "%' "
        else:
            sql += " and p.id = (select id from projects order by id limit 1) "
        cur.execute(sql)
        rowdict = {}
        for row in cur.fetchall():
            rowdict['staffsize'] = row['staffsize']
            rowdict['managercnt'] = row['managercnt']
            rowdict['workercnt'] = row['workercnt']
            rowdict['m_onsitecnt'] = row['m_onsitecnt']
            rowdict['w_onsitecnt'] = row['w_onsitecnt']
            rowdict['m_unsitecnt'] = row['m_unsitecnt']
            rowdict['w_unsitecnt'] = row['w_unsitecnt']
            rowdict['m_nocheckincnt'] = row['m_nocheckincnt']
            rowdict['w_nocheckincnt'] = row['w_nocheckincnt']
            rowdict['m_onsitecnt_degree'] = 0
            if rowdict['managercnt'] > 0 and rowdict['m_onsitecnt'] > 0:
                rowdict['m_onsitecnt_degree'] = round(rowdict['m_onsitecnt']/rowdict['managercnt'] * 100, 2)
            rowdict['w_onsitecnt_degree'] = 0
            if rowdict['workercnt'] > 0 and rowdict['w_onsitecnt'] > 0:
                rowdict['w_onsitecnt_degree'] = round(rowdict['w_onsitecnt']/rowdict['workercnt'] * 100, 2)
            rowdict['m_unsitecnt_degree'] = 0
            if rowdict['managercnt'] > 0 and rowdict['m_unsitecnt'] > 0:
                rowdict['m_unsitecnt_degree'] = round(rowdict['m_unsitecnt']/rowdict['managercnt'] * 100, 2)
            rowdict['w_unsitecnt_degree'] = 0
            if rowdict['workercnt'] > 0 and rowdict['w_unsitecnt'] > 0:
                rowdict['w_unsitecnt_degree'] = round(rowdict['w_unsitecnt']/rowdict['workercnt'] * 100, 2)

        result['code'] = 200
        result['result'] = rowdict
        result['message'] = 'ok'
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

# 获取工作人员考勤数据
def getkinmulist(param):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:
        sql = "with checktime as (  " \
              + "   select " \
              + "       a.employee_id,a.device_id,a.indate,a.intime,b.outtime  " \
              + "   from ( " \
              + "       select " \
              + "           employee_id,device_id,DATE_FORMAT(check_in_time, '%Y-%m-%d') as indate, min(check_in_time) as intime  " \
              + "               from employee_check_record  " \
              + "       group by employee_id,device_id,indate " \
              + "       ) as a  " \
              + "       inner join ( " \
              + "       select " \
              + "           employee_id,device_id,DATE_FORMAT(check_out_time, '%Y-%m-%d') as outdate, max(check_out_time) as outtime  " \
              + "               from employee_check_record  " \
              + "       group by employee_id,device_id,outdate " \
              + "       ) as b  " \
              + "       on a.employee_id = b.employee_id and a.indate = b.outdate " \
              + "       where exists (select c.employee_id,c.device_id,DATE_FORMAT(c.check_in_time, '%Y-%m-%d') as indate from employee_check_record c where c.employee_id = a.employee_id and DATE_FORMAT(c.check_in_time, '%Y-%m-%d') = a.indate group by c.employee_id,c.device_id,indate having count(c.employee_id) = 1) " \
              + "       union " \
              + "       select employee_id,device_id,DATE_FORMAT(check_in_time, '%Y-%m-%d') as indate,check_in_time,(select outtime.check_out_time from   " \
              + "       (select " \
              + "           employee_id,device_id,check_out_time " \
              + "               from employee_check_record  " \
              + "       where check_in_time is null " \
              + "       order by check_in_time " \
              + "       ) outtime where outtime.employee_id = intime.employee_id and intime.check_in_time <= outtime.check_out_time limit 1) as check_out_time " \
              + "       from " \
              + "       (select " \
              + "           employee_id,device_id,check_in_time " \
              + "               from employee_check_record  " \
              + "       where check_out_time is null " \
              + "       order by check_in_time " \
              + "       ) intime " \
              + ") " \
              + "select u.name,u.gender,p.name as project_nm,g.group_name as group_nm,case when u.role = '1' then '管理人员' else '工人' end as rolenm, " \
              + "       DATE_FORMAT(c.intime, '%H:%i:%s') as intime,DATE_FORMAT(c.outtime, '%H:%i:%s') as outtime,concat(TIMESTAMPDIFF(HOUR, c.intime, c.outtime),'小时') as worktime," \
              + "       case when (select count(e.employee_id) from employee_check_record e where e.employee_id = c.employee_id and DATE_FORMAT(e.check_in_time, '%Y-%m-%d') = c.indate group by e.employee_id,e.device_id,DATE_FORMAT(e.check_in_time, '%Y-%m-%d')) > 1 then '多次打卡，异常' " \
              + "       when DATE_FORMAT(c.intime, '%H:%m:%s') > '08:30:00' then '迟到' when DATE_FORMAT(c.outtime, '%H:%m:%s') < '18:00:00' then '早退' when c.intime is null then '缺勤' else '打卡正常' end as notes " \
              + "from  " \
              + "   employee u " \
              + "left outer join " \
              + "   projects p " \
              + "on  " \
              + "   u.project_id = p.id " \
              + "left outer join " \
              + "   project_groups g " \
              + "on   " \
              + "   u.group_id = g.group_id " \
              + "left outer join  " \
              + "   checktime c " \
              + "on  " \
              + "   u.id = c.employee_id  and  c.indate = DATE_FORMAT(now(),'%Y-%m-%d')"
        if param['kubun'] == '1':
           sql += " where u.role = '1' and u.id in (select employee_id from checktime where indate = DATE_FORMAT(now(),'%Y-%m-%d'))"       
        if param['kubun'] == '2':
           sql += " where u.role = '1' and u.id not in (select employee_id from checktime where indate = DATE_FORMAT(now(),'%Y-%m-%d'))"        
        if param['kubun'] == '3':
           sql += " where u.role = '0' and u.id in (select employee_id from checktime where indate = DATE_FORMAT(now(),'%Y-%m-%d'))"       
        if param['kubun'] == '4':
           sql += " where u.role = '0' and u.id not in (select employee_id from checktime where indate = DATE_FORMAT(now(),'%Y-%m-%d'))"

        cur.execute(sql)
        lisrec = []
        for row in cur.fetchall():
            rowdict = {}
            rowdict['name'] = row['name']
            rowdict['gender'] = row['gender']
            rowdict['project'] = row['project_nm']
            rowdict['group'] = row['group_nm']
            rowdict['role'] = row['rolenm']
            rowdict['intime'] = row['intime']
            rowdict['outtime'] = row['outtime']
            rowdict['worktime'] = row['worktime']
            rowdict['notes'] = row['notes']
            lisrec.append(rowdict)
        data = {}
        data['list'] = lisrec
        data['itemCount'] = 48
        data['page'] = 1
        data['pageSize'] = 10
        result['code'] = 200
        result['result'] = data
        result['message'] = 'ok'
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

def strval(data):
    if data is None:
        data = ''
    return data

# 获取工作人员考勤数据csv导出
def getkinmucsv(param):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:
        sql = "with checktime as (  " \
              + "   select " \
              + "       a.employee_id,a.device_id,a.indate,a.intime,b.outtime  " \
              + "   from ( " \
              + "       select " \
              + "           employee_id,device_id,DATE_FORMAT(check_in_time, '%Y-%m-%d') as indate, min(check_in_time) as intime  " \
              + "               from employee_check_record  " \
              + "       group by employee_id,device_id,indate " \
              + "       ) as a  " \
              + "       inner join ( " \
              + "       select " \
              + "           employee_id,device_id,DATE_FORMAT(check_out_time, '%Y-%m-%d') as outdate, max(check_out_time) as outtime  " \
              + "               from employee_check_record  " \
              + "       group by employee_id,device_id,outdate " \
              + "       ) as b  " \
              + "       on a.employee_id = b.employee_id and a.indate = b.outdate " \
              + "       where exists (select c.employee_id,c.device_id,DATE_FORMAT(c.check_in_time, '%Y-%m-%d') as indate from employee_check_record c where c.employee_id = a.employee_id and DATE_FORMAT(c.check_in_time, '%Y-%m-%d') = a.indate group by c.employee_id,c.device_id,indate having count(c.employee_id) = 1) " \
              + "       union " \
              + "       select employee_id,device_id,DATE_FORMAT(check_in_time, '%Y-%m-%d') as indate,check_in_time,(select outtime.check_out_time from   " \
              + "       (select " \
              + "           employee_id,device_id,check_out_time " \
              + "               from employee_check_record  " \
              + "       where check_in_time is null " \
              + "       order by check_in_time " \
              + "       ) outtime where outtime.employee_id = intime.employee_id and intime.check_in_time <= outtime.check_out_time limit 1) as check_out_time " \
              + "       from " \
              + "       (select " \
              + "           employee_id,device_id,check_in_time " \
              + "               from employee_check_record  " \
              + "       where check_out_time is null " \
              + "       order by check_in_time " \
              + "       ) intime " \
              + ") " \
              + "select u.name,u.gender,p.name as project_nm,g.group_name as group_nm,case when u.role = '1' then '管理人员' else '工人' end as rolenm, " \
              + "       DATE_FORMAT(c.intime, '%H:%i:%s') as intime,DATE_FORMAT(c.outtime, '%H:%i:%s') as outtime,concat(TIMESTAMPDIFF(HOUR, c.intime, c.outtime),'小时') as worktime," \
              + "       case when (select count(e.employee_id) from employee_check_record e where e.employee_id = c.employee_id and DATE_FORMAT(e.check_in_time, '%Y-%m-%d') = c.indate group by e.employee_id,e.device_id,DATE_FORMAT(e.check_in_time, '%Y-%m-%d')) > 1 then '多次打卡，异常' " \
              + "       when DATE_FORMAT(c.intime, '%H:%m:%s') > '08:30:00' then '迟到' when DATE_FORMAT(c.outtime, '%H:%m:%s') < '18:00:00' then '早退' when c.intime is null then '缺勤' else '打卡正常' end as notes " \
              + "from  " \
              + "   employee u " \
              + "left outer join " \
              + "   projects p " \
              + "on  " \
              + "   u.project_id = p.id " \
              + "left outer join " \
              + "   project_groups g " \
              + "on   " \
              + "   u.group_id = g.group_id " \
              + "left outer join  " \
              + "   checktime c " \
              + "on  " \
              + "   u.id = c.employee_id  and  c.indate = DATE_FORMAT(now(),'%Y-%m-%d')"
        if param['kubun'] == '1':
           sql += " where u.role = '1' and u.id in (select employee_id from checktime where indate = DATE_FORMAT(now(),'%Y-%m-%d'))"       
        if param['kubun'] == '2':
           sql += " where u.role = '1' and u.id not in (select employee_id from checktime where indate = DATE_FORMAT(now(),'%Y-%m-%d'))"        
        if param['kubun'] == '3':
           sql += " where u.role = '0' and u.id in (select employee_id from checktime where indate = DATE_FORMAT(now(),'%Y-%m-%d'))"       
        if param['kubun'] == '4':
           sql += " where u.role = '0' and u.id not in (select employee_id from checktime where indate = DATE_FORMAT(now(),'%Y-%m-%d'))"
        cur.execute(sql)
        outcsv = '姓名,性别,项目,班组,角色,入场时刻,出场时刻,实际工作时间,勤务状况\n'
        for row in cur.fetchall():
            outcsv += strval(row['name']) + ',' + strval(row['gender']) + ',' + strval(row['project_nm']) + ',' + strval(row['group_nm']) + ',' + strval(row['rolenm']) + ',' + strval(row['intime']) + ',' \
                   + strval(row['outtime']) + ',' + strval(row['worktime']) + ',' + strval(row['notes']) + '\n'

        result['code'] = 200
        result['result'] = outcsv
        result['message'] = 'ok'
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

# 获取工作人员考勤信息csv导出(按年月导出)
def exportcsvformonth(param):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:
        sql = "with checktime as (  " \
              + "   select " \
              + "       a.employee_id,a.device_id,a.indate,a.intime,b.outtime  " \
              + "   from ( " \
              + "       select " \
              + "           employee_id,device_id,DATE_FORMAT(check_in_time, '%Y-%m-%d') as indate, min(check_in_time) as intime  " \
              + "               from employee_check_record  " \
              + "       group by employee_id,device_id,indate " \
              + "       ) as a  " \
              + "       inner join ( " \
              + "       select " \
              + "           employee_id,device_id,DATE_FORMAT(check_out_time, '%Y-%m-%d') as outdate, max(check_out_time) as outtime  " \
              + "               from employee_check_record  " \
              + "       group by employee_id,device_id,outdate " \
              + "       ) as b  " \
              + "       on a.employee_id = b.employee_id and a.indate = b.outdate " \
              + "       where exists (select c.employee_id,c.device_id,DATE_FORMAT(c.check_in_time, '%Y-%m-%d') as indate from employee_check_record c where c.employee_id = a.employee_id and DATE_FORMAT(c.check_in_time, '%Y-%m-%d') = a.indate group by c.employee_id,c.device_id,indate having count(c.employee_id) = 1) " \
              + "       union " \
              + "       select employee_id,device_id,DATE_FORMAT(check_in_time, '%Y-%m-%d') as indate,check_in_time,(select outtime.check_out_time from   " \
              + "       (select " \
              + "           employee_id,device_id,check_out_time " \
              + "               from employee_check_record  " \
              + "       where check_in_time is null " \
              + "       order by check_in_time " \
              + "       ) outtime where outtime.employee_id = intime.employee_id and intime.check_in_time <= outtime.check_out_time limit 1) as check_out_time " \
              + "       from " \
              + "       (select " \
              + "           employee_id,device_id,check_in_time " \
              + "               from employee_check_record  " \
              + "       where check_out_time is null " \
              + "       order by check_in_time " \
              + "       ) intime " \
              + ") " \
              + "select u.name,u.gender,p.name as project_nm,g.group_name as group_nm,case when u.role = '1' then '管理人员' else '工人' end as rolenm,DATE_FORMAT(c.indate, '%Y年%m月%d日') as kinmudate,  " \
              + "       DATE_FORMAT(c.intime, '%H:%i:%s') as intime,DATE_FORMAT(c.outtime, '%H:%i:%s') as outtime,concat(TIMESTAMPDIFF(HOUR, c.intime, c.outtime),'小时') as worktime," \
              + "       case when (select count(e.employee_id) from employee_check_record e where e.employee_id = c.employee_id and DATE_FORMAT(e.check_in_time, '%Y-%m-%d') = c.indate group by e.employee_id,e.device_id,DATE_FORMAT(e.check_in_time, '%Y-%m-%d')) > 1 then '多次打卡，异常' " \
              + "       when DATE_FORMAT(c.intime, '%H:%m:%s') > '08:30:00' then '迟到' when DATE_FORMAT(c.outtime, '%H:%m:%s') < '18:00:00' then '早退' when c.intime is null then '缺勤' else '打卡正常' end as notes " \
              + "from  " \
              + "   employee u " \
              + "left outer join " \
              + "   projects p " \
              + "on  " \
              + "   u.project_id = p.id " \
              + "left outer join " \
              + "   project_groups g " \
              + "on   " \
              + "   u.group_id = g.group_id " \
              + "left outer join  " \
              + "   checktime c " \
              + "on  " \
              + "   u.id = c.employee_id " \
              + "where p.name like '%" + param['key'].split(',')[0] + "%' and DATE_FORMAT(c.indate, '%Y年%m月') = '" + param['key'].split(',')[1] + "' " \
              + " order by kinmudate "

        cur.execute(sql)
        outcsv = '姓名,性别,项目,班组,角色,考勤日,入场时刻,出场时刻,实际工作时间,勤务状况\n'
        for row in cur.fetchall():
            outcsv += strval(row['name']) + ',' + strval(row['gender']) + ',' + strval(row['project_nm']) + ',' + strval(row['group_nm']) + ',' + strval(row['rolenm']) + ',' + strval(row['kinmudate']) + ',' + strval(row['intime']) + ',' \
                   + strval(row['outtime']) + ',' + strval(row['worktime']) + ',' + strval(row['notes']) + '\n'

        result['code'] = 200
        result['result'] = outcsv
        result['message'] = 'ok'
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result
